﻿Imports System.Configuration
Imports System.Data.SqlClient
Imports Comercializacion_BE

Public Class periodosADO


    Public Function RecuperarAnios() As List(Of aniosBE)

        Dim listaAnios As New List(Of aniosBE)
        Dim _anios As aniosBE = Nothing

        Using conexion As New SqlConnection(ConfigurationManager.ConnectionStrings("CnnComercializacion").ConnectionString)

            Using comando As New SqlCommand("select codigoanio, anio from anios", conexion)
                comando.CommandType = CommandType.Text

                conexion.Open()
                Dim reader As SqlDataReader = comando.ExecuteReader()

                If reader.HasRows Then
                    While reader.Read()
                        _anios = New aniosBE
                        _anios.codigoanio = reader("codigoanio")
                        _anios.anio = reader("anio")
                        listaAnios.Add(_anios)
                    End While
                End If
                conexion.Close()

            End Using

        End Using

        Return listaAnios

    End Function

    Public Function RecuperarMeses() As List(Of mesesBE)

        Dim listaMeses As New List(Of mesesBE)
        Dim _meses As mesesBE = Nothing

        Using conexion As New SqlConnection(ConfigurationManager.ConnectionStrings("CnnComercializacion").ConnectionString)

            Using comando As New SqlCommand("select codigomes, mes from meses", conexion)
                comando.CommandType = CommandType.Text

                conexion.Open()
                Dim reader As SqlDataReader = comando.ExecuteReader()

                If reader.HasRows Then
                    While reader.Read()
                        _meses = New mesesBE
                        _meses.codigomes = reader("codigomes")
                        _meses.mes = reader("mes")
                        listaMeses.Add(_meses)
                    End While
                End If
                conexion.Close()

            End Using

        End Using

        Return listaMeses

    End Function



    Public Function ObtenerPeriodosGrid() As DataTable

        Dim dt As New DataTable
        Const sql As String = "Select numeroanio as Año, numeromes as Mes, estado as Estado from periodos"

        Dim conexion As New SqlConnection(ConfigurationManager.ConnectionStrings("CnnComercializacion").ConnectionString)
        Try
            conexion.Open()
            Using da As New SqlDataAdapter(sql, conexion)
                da.Fill(dt)
            End Using
            conexion.Close()
            Return dt
        Catch myException As Exception
            MsgBox("Listar Periodos --> " & myException.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try

    End Function


    Public Function RecuperarEstados() As List(Of estadosBE)

        Dim listaEstados As New List(Of estadosBE)
        Dim _estados As estadosBE = Nothing

        Using conexion As New SqlConnection(ConfigurationManager.ConnectionStrings("CnnComercializacion").ConnectionString)

            Using comando As New SqlCommand("select codigoestado, nombreestado from estados", conexion)
                comando.CommandType = CommandType.Text

                conexion.Open()
                Dim reader As SqlDataReader = comando.ExecuteReader()

                If reader.HasRows Then
                    While reader.Read()
                        _estados = New estadosBE
                        _estados.codigoestado = reader("codigoestado")
                        _estados.nombreestado = reader("nombreestado")
                        listaEstados.Add(_estados)
                    End While
                End If
                conexion.Close()

            End Using

        End Using

        Return listaEstados

    End Function


    Public Function ValidarPeriodo(ByVal anio As String, ByVal mes As String) As List(Of periodosBE)
        Dim listaPeriodos As New List(Of periodosBE)
        Dim _Periodos As periodosBE = Nothing

        Using conexion As New SqlConnection(ConfigurationManager.ConnectionStrings("CnnComercializacion").ConnectionString)

            Using comando As New SqlCommand(String.Format("Select * from periodos where numeroanio='{0}' and numeromes='{1}'", anio, mes), conexion)

                comando.CommandType = CommandType.Text

                conexion.Open()
                Dim reader As SqlDataReader = comando.ExecuteReader()

                If reader.HasRows Then
                    While reader.Read()
                        _Periodos = New periodosBE
                        _Periodos.numeroanio = reader("numeroanio")
                        _Periodos.numeromes = reader("numeromes")
                        listaPeriodos.Add(_Periodos)
                    End While
                End If
                conexion.Close()

            End Using

        End Using

        Return listaPeriodos

    End Function


    Public Function InsertarPeriodos(ByVal periodos As periodosBE) As Boolean

        Dim conexion As New SqlConnection

        conexion.ConnectionString = ConfigurationManager.ConnectionStrings("CnnComercializacion").ConnectionString

        Dim comando As New SqlCommand
        comando.Connection = conexion
        comando.CommandText = "sp_InsertarPeriodos"
        comando.CommandType = CommandType.StoredProcedure

        comando.Parameters.AddWithValue("@codigoperiodo", periodos.codigoperiodo)
        comando.Parameters.AddWithValue("@numeroanio", periodos.numeroanio)
        comando.Parameters.AddWithValue("@numeromes", periodos.numeromes)
        comando.Parameters.AddWithValue("@creadopor", periodos.creadopor)
        comando.Parameters.AddWithValue("@fechacreacion", periodos.fechacreacion)
        comando.Parameters.AddWithValue("@estado", periodos.estado)

        Try
            conexion.Open()

            comando.ExecuteScalar()

            Return True
        Catch ex As Exception
            MsgBox("Error al ingresar los periodos")
            Return False
        Finally
            conexion.Close()
        End Try

    End Function

    Public Function pr_CargarTreeListPeriodoMes() As DataTable
        Try
            Dim cn As New SqlConnection(ConfigurationManager.ConnectionStrings("CnnComercializacion").ConnectionString)
            Using cn
                Dim cmd As New SqlCommand("sp_ListarPeriodosMesesTreeList", cn)
                Using cmd
                    cmd.CommandType = Data.CommandType.StoredProcedure
                    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
                    Dim dt As DataTable = New DataTable("PVTree")
                    da.Fill(dt)
                    Return dt
                End Using
            End Using
        Catch myException As Exception
            MsgBox("periodosADO (pr_CargarTreeListPeriodoMes) --> " & myException.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

End Class
